import json
from datetime import datetime


def get_users(db, logger, data: dict):
    """
    只查询用户列表，检查与当前用户是否存在好友关系

    Args:
        db (_type_): _description_
        logger (_type_): _description_
        data (dict): _description_

    Raises:
        e: _description_

    Returns:
        _type_: _description_
    """
    try:
        user_info = db.get_one("User", {"mac_addr": data.get("mac_addr")})
        conditions = {
            "mac_addr__ne": data["mac_addr"],
        }
        if data["name"] and data["name"].strip():
            conditions["name__contains"] = data["name"].strip()

        user_list = db.get_all(table_name="User", conditions=conditions, order_by="status DESC")
        # 获取未读信息列表(兼容旧版本的私有云)
        for index, item in enumerate(user_list):
            unread_count = db.get_count(
                "Message", {"source": item["mac_addr"], "target": data["mac_addr"], "status": "unread"}
            )
            user_list[index]["unread"] = unread_count
            user_list[index]["category"] = "user"
            user_list[index]["_id"] = f"user_{item['id']}"

            # 检查是否是好友
            if db.get_one(
                "Friendship",
                {
                    "master_id": user_info["id"],
                    "user_id": item["id"],
                },
            ):
                user_list[index]["is_friend"] = True
            else:
                user_list[index]["is_friend"] = False

        user_list.sort(key=lambda x: (x["unread"], x["status"] == "online"), reverse=True)
        logger.info(f"Current number of users: {len(user_list)}")
        return user_list
    except Exception as e:
        raise e


def time_string_to_timestamp(time_str):
    """
    将时间字符串转换为时间戳
    
    Args:
        time_str: 时间字符串，格式如 "2025-10-20 20:28:30"
    
    Returns:
        int: 时间戳（秒级）
    """
    # 定义时间格式
    time_format = "%Y-%m-%d %H:%M:%S"
    
    # 将字符串转换为datetime对象
    dt_obj = datetime.strptime(time_str, time_format)
    
    # 转换为时间戳
    timestamp = int(dt_obj.timestamp())
    
    return timestamp


def get_uts(db, logger, data: dict):
    """
    查询所有用户（最近一条消息）和自己所在的团队

    Args:
        db (_type_): _description_
        logger (_type_): _description_
        data (dict): _description_

    Raises:
        e: _description_

    Returns:
        _type_: _description_
    """
    try:
        sessions = []
        # 根据用户mac地址查询用户信息
        user_info = db.get_one("User", {"mac_addr": data["mac_addr"]})

        # 查询用户列表
        conditions = {
            "mac_addr__ne": data["mac_addr"],
        }
        if data["name"] and data["name"].strip():
            conditions["name__contains"] = data["name"].strip()

        user_list = db.get_all(table_name="User", conditions=conditions, order_by="status DESC")

        # 获取未读信息列表
        for index, item in enumerate(user_list):
            # 获取未读消息数
            unread_count = db.get_count(
                "Message", {"source": item["mac_addr"], "target": user_info["mac_addr"], "status": "unread"}
            )

            # 获取最新一条消息
            last_msg = db.execute_query(
                """
                SELECT id, source, target, context, created_at AS last_time
                FROM Message
                WHERE (source = ? AND target = ?) OR (source = ? AND target = ?)
                ORDER BY last_time DESC
                LIMIT 1
                """,
                (user_info["mac_addr"], item["mac_addr"], item["mac_addr"], user_info["mac_addr"]),
            )
            last_msg = last_msg[0] if last_msg else {}

            # 是否好友关系
            friendship = db.get_one("Friendship", {"master_id": user_info.get("id"), "user_id": item.get("id")})

            sessions.append(
                {
                    **item,
                    "unread": unread_count,
                    "category": "user",
                    "session_id": f"user_{item['id']}",
                    "last_message": last_msg,
                    "last_time": last_msg.get("last_time", "0"),
                    "last_timestamp": time_string_to_timestamp(last_msg["last_time"]) if "last_time" in last_msg else 0,
                    "is_friend": bool(friendship),
                }
            )

        # 查询该用户所属的团队列表
        teams = db.execute_query(
            """
            SELECT ut.*, t.*
            FROM User_Team ut
            JOIN Team t ON ut.team_id = t.id
            WHERE ut.user_id = ?
            """,
            (user_info["id"],),
        )

        for team in teams:
            last_team_msg = db.execute_query(
                """
                SELECT tm.*, tm.created_at AS last_time, u.name AS sender_name
                FROM Team_Message tm
                JOIN User u ON tm.user_id = u.id
                WHERE team_id = ?
                ORDER BY last_time DESC
                LIMIT 1
                """,
                (team["id"],),
            )

            last_team_msg = last_team_msg[0] if last_team_msg else {}

            team_msg_count = db.get_count("Team_Message", {"team_id": team["id"]})
            user_read_count = db.get_count("Team_Message_Read", {"team_id": team["id"], "user_id": user_info["id"]})
            unread = max(team_msg_count - user_read_count, 0)

            sessions.append(
                {
                    "session_id": f"team_{team['id']}",
                    "category": "team",
                    "name": team.get("name", f"team_{team['id']}"),
                    "team_id": team["id"],
                    "last_message": last_team_msg,
                    "last_time": last_team_msg.get("last_time", "0"),
                    "last_timestamp": time_string_to_timestamp(last_team_msg["last_time"]) if "last_time" in last_team_msg else 0,
                    "unread": unread,
                    "status": "online",
                }
            )
        sessions.sort(key=lambda x: x["last_timestamp"], reverse=True)
        logger.info(f"Current number of users: {len(user_list)}")
        return sessions
    except Exception as e:
        raise e


def get_user_teams(db, logger, user_id: int):
    """
    获取用户所属的所有团队列表
    """
    try:
        user_teams = db.get_all("User_Team", {"user_id": user_id})
        teams = [db.get_one("Team", {"id": team["team_id"]}) for team in user_teams]
        logger.info(f"User {user_id} belongs to teams: {str([v['name'] for v in teams])}")
        return teams
    except Exception as e:
        raise e


async def add_friend_request(db, logger, ws_manager, data: dict):
    """
    发送请求以添加好友

    Args:
        db (_type_): _description_
        logger (_type_): _description_
        ws_manager (_type_): _description_
        data (dict): _description_

    Raises:
        e: _description_
    """
    try:
        source_user_mac_addr = data.get("source")
        target_user_mac_addr = data.get("target")
        language = data.get("language", "English")
        validate_msg = data.get("message")
        source_user = db.get_one("User", {"mac_addr": source_user_mac_addr})
        target_user = db.get_one("User", {"mac_addr": target_user_mac_addr})

        if source_user and target_user:
            # add record to database
            msg_id = db.insert_one(
                "Message",
                {
                    "source": source_user_mac_addr,
                    "target": target_user_mac_addr,
                    "context": validate_msg,
                    "is_verify": True,
                    "status": "unread",
                },
            )
            message_content = {
                "category": "message",
                "source": source_user_mac_addr,
                "source_name": source_user["name"],
                "target": target_user_mac_addr,
                "target_name": target_user["name"],
                "is_verify": True,
                "message_id": msg_id,
                "context_type": "text",
                "context": f"请求加为好友: {validate_msg}" if language == "中文" else f"Request to add as a friend: {validate_msg}",
            }
            # send message to target
            if target_user["socket_id"] in ws_manager.active_connections:
                await ws_manager.active_connections[target_user["socket_id"]].send_text(
                    json.dumps(
                        message_content,
                        ensure_ascii=False,
                    )
                )
                logger.info(f"向用户 {data['target']} 发送好友请求：{message_content}")
            else:
                logger.info(f"用户 {data['target']} 未在线，好友请求将通过离线消息发送")
        else:
            logger.info(f"用户 {data['target']} 不存在")
            raise Exception("用户不存在, 请检查mac_addr")
    except Exception as e:
        raise e


def approve_friendship(db, logger, req):
    """
    同意好友申请（建立双向的朋友关系）

    Args:
        db (_type_): _description_
        logger (_type_): _description_
        data (_type_): _description_
    """
    try:
        source_user_mac_addr = req.source
        target_user_mac_addr = req.target

        source_user = db.get_one("User", {"mac_addr": source_user_mac_addr})
        target_user = db.get_one("User", {"mac_addr": target_user_mac_addr})

        if source_user and target_user:
            db.upsert_one(
                "Friendship",
                {
                    "master_id": source_user.get("id"),
                    "user_id": target_user.get("id"),
                },
                {
                    "master_id": source_user.get("id"),
                    "user_id": target_user.get("id"),
                },
            )
            db.upsert_one(
                "Friendship",
                {
                    "master_id": target_user.get("id"),
                    "user_id": source_user.get("id"),
                },
                {
                    "master_id": target_user.get("id"),
                    "user_id": source_user.get("id"),
                },
            )
        else:
            logger.info(f"用户 {target_user_mac_addr} 不存在")
            raise Exception("用户不存在, 请检查mac_addr")
    except Exception as e:
        raise e


def upsert_friendship(db, logger, req):
    """
    更新好友关系（只更新）

    Args:
        db (_type_): _description_
        logger (_type_): _description_
        data (dict): _description_

    Raises:
        e: _description_

    Returns:
        _type_: _description_
    """
    try:
        source_user_mac_addr = req.source
        target_user_mac_addr = req.target
        remark_name = req.remark_name

        source_user = db.get_one("User", {"mac_addr": source_user_mac_addr})
        target_user = db.get_one("User", {"mac_addr": target_user_mac_addr})

        if source_user and target_user:
            friendship_record = db.get_one(
                "Friendship",
                {
                    "master_id": source_user.get("id"),
                    "user_id": target_user.get("id"),
                },
            )
            if friendship_record:
                db.update_one(
                    "Friendship",
                    {
                        "master_id": source_user.get("id"),
                        "user_id": target_user.get("id"),
                        "remark_name": remark_name,
                    },
                    {
                        "id": friendship_record.get("id"),
                    },
                )
            else:
                logger.info(f"用户 {source_user_mac_addr} {target_user_mac_addr} 双方不是好友, 不能更新备注信息")
                raise Exception("双方不是好友, 不能更新备注信息")
        else:
            logger.info(f"用户 {target_user_mac_addr} 不存在")
            raise Exception("用户不存在, 请检查mac_addr")
    except Exception as e:
        raise e
